/**
 * 
 */
package com.lb.store.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;

/**
 * 
 * @author LionBule 2011-6-21 ����10:22:18.
 */
public class TestInsert10w {

    public static void main(String[] args) throws SQLException {
    	TestInsert10w self = new TestInsert10w();
    	//userid=1000010000L
    	long baseUserid=1000000000L;
    	int userCount = 3;
    	int threadCount = 3;
    	
    	for(int i=1;i<=threadCount;i++){
    		long userid = baseUserid + 10000*i;
    		String tableName = getTableName(i);
    		Thread t1 = new Thread(self.new DataDeal(userid, userCount, tableName));
        	t1.setName("Write Thread:"+userid);
        	t1.start();
    	}
    }

    private static String getTableName(int id){
    	if(id>0 && id<10)
    		return "dkv_d000"+id;
    	else if(id>=10 && id<100)
    		return "dkv_d00"+id;
    	else if(id>=100 && id<1000)
    		return "dkv_d0"+id;
    	else if(id>=1000 && id<10000)
    		return "dkv_d"+id;
    	
    	return null;
    }
    
	private static DataSource getDS01(){
		BasicDataSource ds = new BasicDataSource();
		ds.setUsername("ctu3");
		ds.setPassword("ctu3");
		ds.setMaxActive(10);
		ds.setInitialSize(5);
		ds.setUrl("jdbc:mysql://10.250.12.71:3306/dkv_db001?rewriteBatchedStatements=true");
		return ds;
	}
    
	private class DataDeal implements Runnable{
		private long idStart;
		private int userCount;
		private String tableName;
		
		DataDeal(long id, int userCount, String tableName){
			this.idStart=id;
			this.userCount = userCount;
			this.tableName = tableName;
		}
		
		@Override
		public void run() {
	        Connection conn = null;
	        PreparedStatement pstmt = null;
	        try {
	        	DataSource ds = getDS01();
	    		conn = ds.getConnection();
	            conn.setAutoCommit(false);
	            
	            String baseSql = "insert into "+ tableName +" values (?,?,?,?,?, now(), now()) on duplicate key update value=values(value),gmt_modify=values(gmt_modify)";
	    		String valueStr = "qwertyuiopasdf!~@#%^&ghjklzxcvbnm1234567890m$nb&*^%UYRKsdfi)&^$$+)~@vcxzlkjhg567890m$nb&*^%UYRfdsaposm1234567890m$nb&*^%UYRKsdfi)&^$$+)~@vcdfjiuyewq123490qwertyuiopasdfghjkl1812%%*zxcvbnm1234567890mnbvopasdf!~@#%^&ghjklzxcvcxzlkjhgfdsapoiuytrewq1234567890";

	    		long begin = System.currentTimeMillis();
	    		for(long userid=idStart,i=0,tempUserId=0;i<userCount;i++){
	    			tempUserId = userid+i;
	    			
	    			pstmt = conn.prepareStatement(baseSql,ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
	    			
	    			for(int path=2001;path<=2050;path++){
	    				for(int proid=1000;proid<1200;proid++){
	    					int offset = (int)(Math.random()*225);
	    					
	    					pstmt.setLong(1, tempUserId);
	    					pstmt.setInt(2, path);
	    					pstmt.setString(3, "PROPERTY_"+proid);
	    					pstmt.setInt(4, (int)(Math.random()*5));
	    					pstmt.setString(5, valueStr.substring(offset));
	    					
	    					pstmt.addBatch();
	    				}
	    			}
	    			pstmt.executeBatch();
	    			conn.commit();
	    			pstmt.close();
	    		}
	    		long end = System.currentTimeMillis();
	    		if(pstmt!=null) pstmt.close();
	    		conn.close();
	    		String threadName = Thread.currentThread().getName();
	    		System.out.println(threadName+" #Total time(ms):"+(end-begin));
	        }catch(Exception e){
	                e.printStackTrace();
	        }finally{
	        	try {
	                if(pstmt!=null && !pstmt.isClosed())pstmt.close();
	                if(conn!=null && !conn.isClosed())conn.close();
	        	} catch (SQLException e) {
					e.printStackTrace();
				}
	        }
			
		}
		
	}
}
